package models.core;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.builder.ToStringBuilder;

import play.Logger;
import play.db.jpa.GenericModel.JPAQuery;

public class SQLUtils {
	
	public static Object findQuery(String className, Object obj, List<SQLParam> sqlParams,
			Map<String, List<SQLParam>> queryParams, List<SQLOrder> sqlOrder, int page, int size){
		try {
			if (obj != null) {
				return SQLUtils.buildSpecQuery(className, obj).fetch(page, size);
			}else if (sqlParams != null) {
				return SQLUtils.buildSpecQuery(className, sqlParams).fetch(page, size);
			}else if (queryParams != null) {
				return SQLUtils.buildSpecQuery(className, queryParams, sqlOrder).fetch(page, size);
			}else if (sqlOrder != null){
				return SQLUtils.buildSpecQuery(className, null, sqlOrder).fetch(page, size);
			}else{
				return SQLUtils.buildSpecQuery(className, null).fetch(page, size);
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static JPAQuery buildSpecQuery(String className, Object queryParams) throws Exception {
		List<Object> sqlValues = new ArrayList<Object>();
		boolean flag = false;
		StringBuilder sql = new StringBuilder();
		if (queryParams != null) {
			sql.append("by");
			Field[] fields = queryParams.getClass().getDeclaredFields();
			for (Field field : fields) {
				if (field.get(queryParams) != null) {
					if (flag)
						sql.append("And");
					String str = field.getName();
					sql.append(str.replaceFirst(str.substring(0, 1), str.substring(0, 1).toUpperCase()));
					sqlValues.add(field.get(queryParams));
					flag = true;
				}
			}
		}
		Logger.info("buildSpecQuey SQL:" + sql.toString());

		return findSQL(className, sql.toString(), getParams(sqlValues));
	}

	public static JPAQuery buildSpecQuery(String className, List<SQLParam> sqlParams) throws Exception {
		List<Object> sqlValues = new ArrayList<Object>();
		boolean flag = false;
		StringBuilder sql = new StringBuilder();
		if (sqlParams != null) {
			sql.append("by");
			for (SQLParam sqlParam : sqlParams) {
				if (flag)
					sql.append("And");
				String str = sqlParam.fieldName;
				sql.append(str.replaceFirst(str.substring(0, 1), str.substring(0, 1).toUpperCase()) + sqlParam.operator);
				if (sqlParam.value != null)
					sqlValues.add(sqlParam.value);
				flag = true;
			}
		}
		Logger.info("buildSpecQuey SQL:" + sql.toString());

		return findSQL(className, sql.toString(), getParams(sqlValues));

	}

	public static JPAQuery buildSpecQuery(String className, Map<String, List<SQLParam>> queryParams,
			List<SQLOrder> orderParams) throws Exception {
		List<Object> sqlValues = new ArrayList<Object>();
		boolean flag = false;
		StringBuilder sql = new StringBuilder();

		if (queryParams != null) {
			for (String key : queryParams.keySet()) {
				List<SQLParam> sqlParams = queryParams.get(key);
				for (SQLParam sqlParam : sqlParams) {
					if (flag) {
						sql.append(key);
					}
					sql.append(sqlParam.fieldName + sqlParam.operator.getName());
					if (sqlParam.value != null)
						sqlValues.add(sqlParam.value);
					flag = true;
				}

			}
		}
		if (orderParams != null) {
			sql.append(SQLOperator.OrderBy.getName());
			for (SQLOrder order : orderParams) {
				sql.append(order.fieldName);
				sql.append(order.order.getName());
				sql.append(", ");
			}
			sql.deleteCharAt(sql.lastIndexOf(","));
		}
		Logger.info("buildSpecQuey SQL:" + sql.toString());

		return findSQL(className, sql.toString(), getParams(sqlValues));
	}

	private static Object[] getParams(List<Object> sqlValues) {
		Object[] params = new Object[sqlValues.size()];
		for (int i = 0; i < sqlValues.size(); i++) {
			params[i] = sqlValues.get(i);
		}
		Logger.info("buildSpecQuey SQL:" + ToStringBuilder.reflectionToString(params));
		return params;
	}

	private static JPAQuery findSQL(String className, String sql, Object[] params) throws Exception {
		Class clazz = Class.forName(className);
		Method find = clazz.getMethod("find", String.class, Object[].class);
		return (JPAQuery) find.invoke(clazz, sql.toString(), params);
	}
	
	/**
	 * 例子
	 */
	
	public void example(){
		
		List<SQLParam> sqlParams = new ArrayList<SQLParam>();
		sqlParams.add(new SQLParam("articleTitle", SQLOperator.Ilike, "%haha"));
		sqlParams.add(new SQLParam("articleTitle", SQLOperator.Ilike, "%haha"));
		
		List<SQLParam> sqlParams1 = new ArrayList<SQLParam>();
		sqlParams1.add(new SQLParam("articleTitle", SQLOperator.Ilike, "%haha"));
		
		List<SQLOrder> sqlOrder = new ArrayList<SQLOrder>();
		sqlOrder.add(new SQLOrder("articleTitle", SQLOperator.Asc));
		sqlOrder.add(new SQLOrder("articleTitle", SQLOperator.Desc));
		
		Map<String,List<SQLParam>> map = new HashMap<String,List<SQLParam>>();
		map.put(SQLOperator.And.getName(), sqlParams);
		map.put(SQLOperator.Or.getName(), sqlParams1);
	}

}
